package com.mini.repository;

import com.mini.model.BusinessRequirementPlanTableAndForm;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 项目名称：traditional-business
 * 类 名 称：BusinessRequirementPlanTableFormRepository
 * 类 描 述：TODO
 * 创建时间：2020/12/16 下午3:44
 * 创 建 人：zhanghuiying
 */
@Repository
public interface BusinessRequirementPlanTableFormRepository extends JpaRepository<BusinessRequirementPlanTableAndForm,Long>, JpaSpecificationExecutor<BusinessRequirementPlanTableAndForm> {

    @Query(value = "SELECT\n" +
            "\t t.project_id,\n" +
            "\t f.id,\n" +
            "\t f.table_id,\n" +
            "\t f.need_id, \n" +
            "\t f.channel_code,\n" +
            "\t f.province,\n" +
            "\t n.need_count,\n" +
            "\t f.allot_count\n"+
            "FROM\n" +
            "\t business_requirement_plan_table t\n" +
            "\t LEFT JOIN business_requirement_plan_form f ON t.id = f.table_id \n" +
            "\t LEFT JOIN business_requirement_plan_need n ON f.need_id=n.id \n"+
            "WHERE\n" +
            "\t t.base_id = ?1 AND t.table_delete=0 AND n.need_delete=0 ",nativeQuery = true)
    List<BusinessRequirementPlanTableAndForm> findByBaseIdInTableAndForm(Long id);
    @Query(value = "SELECT\n" +
            "\t t.project_id,\n" +
            "\t f.id,\n" +
            "\t f.table_id,\n" +
            "\t f.need_id, \n" +
            "\t f.channel_code,\n" +
            "\t f.province,\n" +
            "\t n.need_count,\n" +
            "\t f.allot_count\n"+
            "FROM\n" +
            "\t business_requirement_plan_table t\n" +
            "\t LEFT JOIN business_requirement_plan_form f ON t.id = f.table_id \n" +
            "\t LEFT JOIN business_requirement_plan_need n ON f.need_id=n.id \n"+
            "WHERE\n" +
            "\t t.id = ?1 AND t.table_delete=0 AND n.need_delete=0 ",nativeQuery = true)
    List<BusinessRequirementPlanTableAndForm> findByTableId(Long id);
}
